package com.iscc.fake.dao.Impl;

import com.iscc.fake.domain.Address;
import com.iscc.fake.dao.AddressDAO;
import com.iscc.fake.util.DataSourceUtile;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class AddressDaoImpl implements AddressDAO {

    //查询数据--------------------------------------------------------------
    @Override
    public List<Address> selectAddress() {

        List<Address> list=new ArrayList<>();
        String sql="SELECT * from address";

        Connection conn= null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            conn = DataSourceUtile.getConnection();

            //预处理对象
            ps =conn.prepareStatement(sql);
            //执行SQL语句，查询-executeQuery，增删改-executeUpdate():返回int:影响行数。
           rs = ps.executeQuery();
            while (rs.next()) {
                Address address=new Address();
                address.setAddno(rs.getInt(1));
                address.setCity(rs.getString(2));
                address.setAddress(rs.getString(3));
                address.setAddphone(rs.getInt(4));
                address.setUno(rs.getInt(5));
                address.setRemark(rs.getString(6));
                address.setName(rs.getString(7));
                list.add(address);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DataSourceUtile.closeReource(rs,ps,conn);
        }


        return list;
    }

    //添加数据---------------------------------------------------------------


    @Override
    public int add(Address address) {
        String sql = "INSERT INTO address(city,address,addphone,uno,remark,uname) VALUES(?,?,?,?,?,?)";
        int result = -1;
        Connection conn=null;
        PreparedStatement psmt=null;
        ResultSet rs=null;
        try {
             conn = DataSourceUtile.getConnection();
            psmt = conn.prepareStatement(sql);
            psmt.setString(1,address.getCity());
            psmt.setString(2,address.getAddress());
            psmt.setInt(3,address.getAddphone());
            psmt.setInt(4,address.getUno());
            psmt.setString(5,address.getRemark());
            psmt.setString(6,address.getName());

            result = psmt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DataSourceUtile.closeReource(psmt,conn);
        }


        return result;
    }


    //更新数据-------------------------------------------------------------------
    @Override
    public int update(Address address) {
        String sql = "UPDATE address SET city = ? , address = ? ,  addphone = ? , uno = ? , remark = ? , uname = ? WHERE addno =?";
        int result=-1;

        Connection conn=null;
        PreparedStatement ps=null;
        try {
            conn = DataSourceUtile.getConnection();
            ps=conn.prepareStatement(sql);
            ps.setString(1,address.getCity());
            ps.setString(2,address.getAddress());
            ps.setInt(3,address.getAddphone());
            ps.setInt(4,address.getUno());
            ps.setString(5,address.getRemark());
            ps.setString(6,address.getName());
            ps.setInt(7,address.getAddno());
            result=ps.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DataSourceUtile.closeReource(ps,conn);
        }


        return result;
    }

    //删除数据-------------------------------------------------------------------
    @Override
    public int del(int addno) {
        String sql = "DELETE FROM address WHERE addno = ?";
        int result = -1;
        Connection conn =null;
        PreparedStatement psmt=null;
        try {
            conn = DataSourceUtile.getConnection();
            psmt = conn.prepareStatement(sql);
            psmt.setInt(1,addno);
            result = psmt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DataSourceUtile.closeReource(psmt,conn);
        }
        return result;

    }
}
